﻿select distinct s.Syno collate Cyrillic_general_CI_AS as Syno
into #tmp
from Entities e
inner join EntityFBTypes fbt on fbt.EntityID = e.EntityID
inner join FreebaseTypes ft on ft.id = fbt.FBTypeID
inner join SynonymEntities se on se.EntityID = e.EntityID and se.IsActive = 1
inner join Synonyms s on s.ID = se.SynoID
where ft.Name = '/people/person' 
	and s.Syno like '% %' 
	and s.Syno not like '%[,)(.0-9]%'


insert into dbo.Names
select substring(syno, 1, charindex(' ', syno) - 1)
from #tmp
group by substring(syno, 1, charindex(' ', syno) - 1)
having count(*) > 10

insert into dbo.LastNames
select reverse(substring(reversed, 1, case when charindex(' ', reversed) - 1 > 0 then charindex(' ', reversed) - 1 else 0 end))
from (
	select reverse(syno) as reversed
	from #tmp
) t
group by reverse(substring(reversed, 1, case when charindex(' ', reversed) - 1 > 0 then charindex(' ', reversed) - 1 else 0 end))
having count(*) > 10

--Now knowing popular names
--We can fill name synonym table

select e.EntityID, substring(s.syno collate Cyrillic_General_CI_AS, 1, charindex(' ', s.syno)) as name
into #tmpCorr
from Entities e
inner join EntityFBTypes fbt on fbt.EntityID = e.EntityID
inner join FreebaseTypes ft on ft.id = fbt.FBTypeID
inner join SynonymEntities se on se.EntityID = e.EntityID and se.IsActive = 1
inner join Synonyms s on s.ID = se.SynoID
where ft.Name = '/people/person'
	and s.Syno like '% %' 
	and s.Syno not like '%[,)(.0-9]%'

create clustered index ix_tmpcorr on #tmpCorr(entityid, name)

select name, count(*) as cnt
into #nameFreq
from #tmpCorr
group by name

create clustered index ix_name on #nameFreq(name)

select ttt.name1, ttt.name2, count(*) as cnt
into #corrHyp
from (
	select distinct t.name as name1, tt.name as name2, t.entityID
	from #tmpCorr t
	inner join #tmpCorr tt on t.EntityID = tt.EntityID 
		and t.name < tt.name
) ttt
group by ttt.name1, ttt.name2
having count(*) > 10

insert into NameSynonyms
select t4.name1, t4.name2
from #corrHyp t4
inner join #nameFreq n on n.name = t4.name1
inner join #nameFreq nn on nn.name = t4.name2
where n.cnt*1.0/nn.cnt < 100
	and nn.cnt*1.0/n.cnt < 100
	and t4.cnt*1.0/n.cnt > 0.025
	and t4.cnt*1.0/nn.cnt > 0.025
	and not (t4.cnt*1.0/nn.cnt < 0.05 and t4.cnt*1.0/n.cnt < 0.05)